﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace SIS.DataAccess
{
	/// <summary>
	/// Author: hieunv
	/// </summary>
	[Serializable]
	class Takes : System.Data.DataTable
	{
		public const string STUDENT_ID = "ID";
		public const string COURSE_ID = "course_id";
		public const string SECTION_ID = "sec_id";
		public const string SEMESTER = "semester";
		public const string YEAR = "year";
		public const string GRADE = "grade";
        /// <summary>
        /// 
        /// </summary>
		public Takes()
			: base("Takes")
		{
			var studentID = new System.Data.DataColumn(STUDENT_ID, typeof(string));
			studentID.AllowDBNull = false;
			this.Columns.Add(studentID);

			var courseID = new System.Data.DataColumn(COURSE_ID, typeof(string));
			courseID.AllowDBNull = false;
			this.Columns.Add(courseID);

			var sectionID = new System.Data.DataColumn(SECTION_ID, typeof(string));
			sectionID.AllowDBNull = false;
			this.Columns.Add(sectionID);

			var semester = new System.Data.DataColumn(SEMESTER, typeof(string));
			semester.AllowDBNull = false;
			this.Columns.Add(semester);

			var year = new System.Data.DataColumn(YEAR, typeof(int));
			year.AllowDBNull = false;
			this.Columns.Add(year);

			var grade = new System.Data.DataColumn(GRADE, typeof(string));
			grade.AllowDBNull = true;
			this.Columns.Add(grade);
			this.PrimaryKey = new System.Data.DataColumn[] { studentID, courseID, sectionID, semester, year };
		}

		protected override Type GetRowType()
		{
			return typeof(Business.Takes);
		}

		protected override DataRow NewRowFromBuilder(DataRowBuilder builder)
		{
			return new Business.Takes(builder);
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="take"></param>
		public bool Insert(Business.Takes take)
		{
			try
			{
				this.Rows.Add(take);
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@studentId", SqlDbType.VarChar, 5, STUDENT_ID));
				parameters.Add(new SqlParameter("@courseId", SqlDbType.VarChar, 8, COURSE_ID));
				parameters.Add(new SqlParameter("@sectionId", SqlDbType.VarChar, 8, SECTION_ID));
				parameters.Add(new SqlParameter("@semester", SqlDbType.VarChar, 6, SEMESTER));
				parameters.Add(new SqlParameter("@year", SqlDbType.Int, 4, YEAR));
				string query = String.Format("INSERT INTO takes({0}, {1}, {2}, {3}, {4}) VALUES(@studentId, @courseId, @sectionId, @semester, @year)", STUDENT_ID, COURSE_ID, SECTION_ID, SEMESTER, YEAR);
				DataServices.ExecuteQuery(QueryType.INSERT, CommandType.Text, query, parameters, this);
				return true;
			}
			catch (ConstraintException exc)
			{
				System.Windows.Forms.MessageBox.Show(exc.Message);
				return false;
			}
			catch (SqlException exc)
			{
				this.RejectChanges();
				System.Windows.Forms.MessageBox.Show(exc.Message);
				return false;
			}
			catch (System.Exception exc)
			{
				System.Windows.Forms.MessageBox.Show(exc.ToString());
				return false;
			}
			
		}
        /// <summary>
        /// 
        /// </summary>
        /// <param name="take"></param>
		public void Update(Business.Takes take)
		{
			string query = "UPDATE takes SET ID=@a1, course_id=@a2, sec_id=@a3, semester=@a4, year=@a5, grade=@a6 WHERE ID=@p1 AND course_id=@p2 AND sec_id=@p3 AND semester=@p4 AND year=@p5";
			List<SqlParameter> parameters = new List<SqlParameter>();
			parameters.Add(new SqlParameter("@a1", SqlDbType.VarChar, 5, STUDENT_ID));
			parameters.Add(new SqlParameter("@a2", SqlDbType.VarChar, 8, COURSE_ID));
			parameters.Add(new SqlParameter("@a3", SqlDbType.VarChar, 8, SECTION_ID));
			parameters.Add(new SqlParameter("@a4", SqlDbType.VarChar, 6, SEMESTER));
			parameters.Add(new SqlParameter("@a5", SqlDbType.Int, 4, YEAR));
			parameters.Add(new SqlParameter("@a6", SqlDbType.VarChar, 2, GRADE));
			var p1 = new SqlParameter("@p1", SqlDbType.VarChar, 5, STUDENT_ID);
			p1.SourceVersion = DataRowVersion.Original;
			parameters.Add(p1);
			var p2 = new SqlParameter("@p2", SqlDbType.VarChar, 8, COURSE_ID);
			p2.SourceVersion = DataRowVersion.Original;
			parameters.Add(p2);
			var p3 = new SqlParameter("@p3", SqlDbType.VarChar, 8, SECTION_ID);
			p3.SourceVersion = DataRowVersion.Original;
			parameters.Add(p3);
			var p4 = new SqlParameter("@p4", SqlDbType.VarChar, 6, SEMESTER);
			p4.SourceVersion = DataRowVersion.Original;
			parameters.Add(p4);
			var p5 = new SqlParameter("@p5", SqlDbType.Int, 4, YEAR);
			p5.SourceVersion = DataRowVersion.Original;
			parameters.Add(p5);
			DataServices.ExecuteQuery(QueryType.UPDATE, CommandType.Text, query, parameters, this);
		}
        /// <summary>
        /// 
        /// </summary>
        /// <param name="take"></param>
		public void Delete(Business.Takes take)
		{
			try
			{
				(take as DataRow).Delete();
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@studentId", SqlDbType.VarChar, 5, STUDENT_ID));
				parameters.Add(new SqlParameter("@courseId", SqlDbType.VarChar, 8, COURSE_ID));
				parameters.Add(new SqlParameter("@sectionId", SqlDbType.VarChar, 8, SECTION_ID));
				parameters.Add(new SqlParameter("@semester", SqlDbType.VarChar, 6, SEMESTER));
				parameters.Add(new SqlParameter("@year", SqlDbType.Int, 4, YEAR));
				DataServices.ExecuteQuery(QueryType.DELETE, CommandType.Text, "DELETE FROM takes WHERE ID=@studentId AND course_id=@courseId AND sec_id=@sectionId AND semester=@semester AND year=@year", parameters, this);
			}
			catch (System.Exception ex)
			{
				System.Windows.Forms.MessageBox.Show(ex.ToString());
			}
			
		}

		public void Fill()
		{
			DataAccess.DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT * FROM takes", null, this);
		}
	}
}
